﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using ElingOA6_Admin_DSJCJ.Common;
using ElingOA6_Admin_DSJCJ.Models;
using System.Text;
using System.Data;

namespace ElingOA6_Admin_DSJCJ.Areas.Admin.Controllers
{
    /// <summary>
    /// 用品统计
    /// </summary>
    /// 
    [OutputCache(Duration = 0, VaryByParam = "none")]
    public class OfficeItemCountController : AdminBaseController
    {
        //
        // GET: /Admin/OfficeItemCount/
        /// <summary>
        /// 用品统计主页
        /// </summary>
        /// <returns></returns>
        [AuthorityActionFilter("m_OfficeItemCount", Order = 1)]
        [LogPageActionFilter("用品统计", Order = 2)]
        public ActionResult Index()
        {

            return View();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public ActionResult OfficeItemCountList(string itemClsNo = "", string startDate = "", string endDate="")
        {

            if (itemClsNo == null || itemClsNo == "")
            {
                ViewData["itemClsNo"] = "00";
            }
            else
            {
                ViewData["itemClsNo"] = itemClsNo;
            }

            List<Model.ItemCls> itemClsModel = CommonCode.GetItemList(GlobalConstant.ENTITYNO);
            List<SelectListItem> list = new List<SelectListItem>();
            SelectListItem item = new SelectListItem();
            item.Text = "全部";
            item.Value = "00";
            list.Add(item);
            foreach (Model.ItemCls itemCls in itemClsModel)
            {
                item = new SelectListItem();
                item.Text = itemCls.FItemClsName;
                item.Value = itemCls.FItemClsNo;
                list.Add(item);
            }

            ViewData["clsNoList"] = list;


            List<Model.ItemOfficeCount> itemCountList = GetItemCountList(itemClsNo, startDate, endDate);

            ViewData["itemClsNo"] = itemClsNo;
            ViewData["startDate"] = startDate;
            ViewData["endDate"] = endDate;
            
            return View(itemCountList);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public ActionResult OfficeItemCountListPartial()
        {
            string itemClsNo = Request.Params["itemClsNo"];
            string startDate = Request.Params["startDate"];
            string endDate = Request.Params["endDate"];

            List<Model.ItemOfficeCount> itemCountList = GetItemCountList(itemClsNo, startDate, endDate);

            return View(itemCountList);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private List<Model.ItemOfficeCount> GetItemCountList(string itemClsNo, string startDate, string endDate)
        {
            List<Model.ItemOfficeCount> itemCountList = new List<Model.ItemOfficeCount>();

            BLL.ItemOfficeCount itemCountBll = new BLL.ItemOfficeCount();

            DataSet ds = itemCountBll.GetAll(itemClsNo, startDate, endDate);

            itemCountList = itemCountBll.DataTableToListItemCount(ds.Tables[0]);
            decimal sum1 = 0;
            decimal sum2 = 0;
            decimal sum3 = 0;
            decimal sum4 = 0;
            decimal sum5 = 0;
            decimal sum6 = 0;
            foreach (Model.ItemOfficeCount model in itemCountList) 
            {
                model.FQty = model.qty1 - decimal.Parse(model.FApplyQty == "" ? "0" : model.FApplyQty);

                sum1 += model.FQty;
                sum2 += model.qty1;
                sum3 += model.amount1;
                sum4 += model.qty2;
                sum5 += decimal.Parse(model.FApplyQty == "" ? "0" : model.FApplyQty);
                sum6 += model.amount2;

            }

            var query = from p in itemCountList group p by 
                            new { p.FItemClsNo, p.FItemClsName} into g
                        select new { g.Key, Sum1 = g.Sum(p => p.FQty),
                                     Sum2 = g.Sum(p => p.qty1),
                                     Sum3 = g.Sum(p => p.amount1),
                                     Sum4 = g.Sum(p => p.qty2),
                                     Sum5 = g.Sum(p => p.FApplyQtyDec),
                                     Sum6 = g.Sum(p => p.amount2)
                        };

           
            foreach (var q in query)
            {
                int i = 0;
                List<Model.ItemOfficeCount> select = (from nm in itemCountList 
                                                      where nm.FItemClsNo == q.Key.FItemClsNo 
                                                      select nm).ToList();
                foreach (Model.ItemOfficeCount Itemmodel in select)
                {         
                    Itemmodel.Feq = i;
                    i++;
                }

                Model.ItemOfficeCount model = new Model.ItemOfficeCount();
                model.FItemClsNo = q.Key.FItemClsNo;
                model.FItemClsName = q.Key.FItemClsName;
                model.FQty = q.Sum1;
                model.qty1 = q.Sum2;
                model.amount1 = q.Sum3;
                model.qty2 = q.Sum4;               
                model.FApplyQty = q.Sum5.ToString();
                model.amount2 = q.Sum6;
                model.Feq = i;
                model.FItemName = "小计";
                itemCountList.Add(model);                
            }

            ViewData["sum1"] = sum1;
            ViewData["sum2"] = sum2;
            ViewData["sum3"] = sum3;
            ViewData["sum4"] = sum4;
            ViewData["sum5"] = sum5;
            ViewData["sum6"] = sum6;
                      
            return itemCountList;       
        }

        /// <summary>
        /// 
        /// </summary>
        /// 
        int rowIndex = 1;
        public void OfficeItemCountExportToExcel(string itemClsNo, string startDate, string endDate) 
        {
            List<Model.XlsModel> xlsModels = new List<Model.XlsModel>();

            Model.XlsModel xlsModel = new Model.XlsModel();

            #region 静态数据
            //第一行
            xlsModel = new Model.XlsModel();
            xlsModel.RowIndex = 1;
            xlsModel.Title = "用品名称";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModels.Add(xlsModel);

            xlsModel = new Model.XlsModel();
            xlsModel.RowIndex = 1;
            xlsModel.Title = "规格";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModels.Add(xlsModel);

            xlsModel = new Model.XlsModel();
            xlsModel.RowIndex = 1;
            xlsModel.Title = "总采购数";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModels.Add(xlsModel);

            xlsModel = new Model.XlsModel();
            xlsModel.RowIndex = 1;
            xlsModel.Title = "总采购金额";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModels.Add(xlsModel);

            xlsModel = new Model.XlsModel();
            xlsModel.RowIndex = 1;
            xlsModel.Title = "总申请数";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModels.Add(xlsModel);

            xlsModel = new Model.XlsModel();
            xlsModel.RowIndex = 1;
            xlsModel.Title = "总实际领用数";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModels.Add(xlsModel);

            xlsModel = new Model.XlsModel();
            xlsModel.RowIndex = 1;
            xlsModel.Title = "总领用金额";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModels.Add(xlsModel);

            xlsModel = new Model.XlsModel();
            xlsModel.RowIndex = 1;
            xlsModel.Title = "库存";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModels.Add(xlsModel);
            #endregion


            List<Model.XlsModel> xlsModels1 = createXlsTable(itemClsNo, startDate, endDate);
            if (xlsModels1 != null)
            {
                xlsModels.AddRange(xlsModels1);
            }

            List<Model.XlsModel_Column> xlsModelColumns = new List<Model.XlsModel_Column>();
            Model.XlsModel_Column xmc = new Model.XlsModel_Column();
            xmc.ColumnIndexEnd = 1;
            xmc.ColumnIndexStart = 0;
            xmc.Width = 25;
            xlsModelColumns.Add(xmc);

            xmc = new Model.XlsModel_Column();
            xmc.ColumnIndexEnd = 5;
            xmc.ColumnIndexStart = 2;
            xmc.Width = 15;
            xlsModelColumns.Add(xmc);
            GenerateUtil.ExportToExcelReturnPath(".cls", "用品统计", rowIndex, xlsModels, xlsModelColumns, new List<Model.XlsModel_Row>());

        
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="itemClsNo"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        private List<Model.XlsModel> createXlsTable(string itemClsNo, string startDate, string endDate)
        {
            List<Model.XlsModel> xlsModels = new List<Model.XlsModel>();

            BLL.ItemOfficeCount itemCountBll = new BLL.ItemOfficeCount();

            DataSet ds = itemCountBll.GetAll(itemClsNo, startDate, endDate);

            itemClsNo = "";

            List<Model.ItemOfficeCount> itemCountList = itemCountBll.DataTableToListItemCount(ds.Tables[0]);

            int i = 0;
            int count = 0;

            decimal sum1 = 0;
            decimal sum2 = 0;
            decimal sum3 = 0;
            decimal sum4 = 0;
            decimal sum5 = 0;
            decimal sum6 = 0;
            foreach (Model.ItemOfficeCount model in itemCountList) 
            {
                model.FQty = model.qty1 - decimal.Parse(model.FApplyQty == "" ? "0" : model.FApplyQty);
                sum1 += model.qty1;
                sum2 += model.amount1;
                sum3 += model.qty2;
                sum4 += decimal.Parse(model.FApplyQty == "" ? "0" : model.FApplyQty);
                sum5 += model.amount2;
                sum6 += model.FQty;
            }
            decimal count1 = 0;
            decimal count2 = 0;
            decimal count3 = 0;
            decimal count4 = 0;
            decimal count5 = 0;
            decimal count6 = 0;
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    count++;

                    Model.XlsModel xlsModel = new Model.XlsModel();

                    if (itemClsNo != dr["FItemClsNo"].ToString())
                    {
                        rowIndex++;
                        itemClsNo = dr["FItemClsNo"].ToString();
                        i = 0;
                        count1 = 0;
                        count2 = 0;
                        count3 = 0;
                        count4 = 0;
                        count5 = 0;
                        count6 = 0;

                        xlsModel = new Model.XlsModel();
                        xlsModel.RowIndex = rowIndex;
                        xlsModel.Title = "用品类别:" + dr["FItemClsName"].ToString();
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModels.Add(xlsModel);

                        i++;
                        count1 += decimal.Parse(dr["qty1"].ToString());
                        count2 += decimal.Parse(dr["amount1"].ToString());
                        count3 += decimal.Parse(dr["qty2"].ToString());
                        count4 += decimal.Parse(dr["FApplyQty"].ToString() == "" ? "0" : dr["FApplyQty"].ToString());
                        count5 += decimal.Parse(dr["amount2"].ToString());
                        count6 += (decimal.Parse(dr["qty1"].ToString()) - decimal.Parse(dr["FApplyQty"].ToString() == "" ? "0" : dr["FApplyQty"].ToString()));

                    }
                    else
                    {
                        i++;
                        count1 += decimal.Parse(dr["qty1"].ToString());
                        count2 += decimal.Parse(dr["amount1"].ToString());
                        count3 += decimal.Parse(dr["qty2"].ToString());
                        count4 += decimal.Parse(dr["FApplyQty"].ToString() == "" ? "0" : dr["FApplyQty"].ToString());
                        count5 += decimal.Parse(dr["amount2"].ToString());
                        count6 += (decimal.Parse(dr["qty1"].ToString()) - decimal.Parse(dr["FApplyQty"].ToString() == "" ? "0" : dr["FApplyQty"].ToString()));

                    }

                    rowIndex++;
                    xlsModel = new Model.XlsModel();
                    xlsModel.RowIndex = rowIndex;
                    xlsModel.Title = dr["FItemName"].ToString();
                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                    xlsModels.Add(xlsModel);

                    xlsModel = new Model.XlsModel();
                    xlsModel.RowIndex = rowIndex;
                    xlsModel.Title = dr["FItemSpec"].ToString();
                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                    xlsModels.Add(xlsModel);

                    xlsModel = new Model.XlsModel();
                    xlsModel.RowIndex = rowIndex;
                    xlsModel.Title = dr["qty1"].ToString();
                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                    xlsModels.Add(xlsModel);

                    xlsModel = new Model.XlsModel();
                    xlsModel.RowIndex = rowIndex;
                    xlsModel.Title = dr["amount1"].ToString();
                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                    xlsModels.Add(xlsModel);


                    xlsModel = new Model.XlsModel();
                    xlsModel.RowIndex = rowIndex;
                    xlsModel.Title = dr["qty2"].ToString();
                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                    xlsModels.Add(xlsModel);

                    xlsModel = new Model.XlsModel();
                    xlsModel.RowIndex = rowIndex;
                    xlsModel.Title = dr["FApplyQty"].ToString();
                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                    xlsModels.Add(xlsModel);


                    xlsModel = new Model.XlsModel();
                    xlsModel.RowIndex = rowIndex;
                    xlsModel.Title = dr["amount2"].ToString();
                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                    xlsModels.Add(xlsModel);

                    xlsModel = new Model.XlsModel();
                    xlsModel.RowIndex = rowIndex;
                    xlsModel.Title = (decimal.Parse(dr["qty1"].ToString()) - decimal.Parse(dr["FApplyQty"].ToString())).ToString();
                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                    xlsModels.Add(xlsModel);

                    var query = itemCountList.Where(c => c.FItemClsNo == itemClsNo).ToList();
                    if (i == query.Count())
                    {
                        rowIndex++;
                        xlsModel = new Model.XlsModel();
                        xlsModel.RowIndex = rowIndex;
                        xlsModel.Title = "小计";
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModels.Add(xlsModel);

                        xlsModel = new Model.XlsModel();
                        xlsModel.RowIndex = rowIndex;
                        xlsModel.Title = "";
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModels.Add(xlsModel);

                        xlsModel = new Model.XlsModel();
                        xlsModel.RowIndex = rowIndex;
                        xlsModel.Title = count1.ToString();
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModels.Add(xlsModel);

                        xlsModel = new Model.XlsModel();
                        xlsModel.RowIndex = rowIndex;
                        xlsModel.Title = count2.ToString();
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModels.Add(xlsModel);


                        xlsModel = new Model.XlsModel();
                        xlsModel.RowIndex = rowIndex;
                        xlsModel.Title = count3.ToString();
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModels.Add(xlsModel);

                        xlsModel = new Model.XlsModel();
                        xlsModel.RowIndex = rowIndex;
                        xlsModel.Title = count4.ToString();
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModels.Add(xlsModel);


                        xlsModel = new Model.XlsModel();
                        xlsModel.RowIndex = rowIndex;
                        xlsModel.Title = count6.ToString();
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModels.Add(xlsModel);

                        xlsModel = new Model.XlsModel();
                        xlsModel.RowIndex = rowIndex;
                        xlsModel.Title = count6.ToString();
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModels.Add(xlsModel);
                    }
                    if (count == itemCountList.Count())
                    {
                        rowIndex++;
                        xlsModel = new Model.XlsModel();
                        xlsModel.RowIndex = rowIndex;
                        xlsModel.Title = "合计";
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModels.Add(xlsModel);

                        xlsModel = new Model.XlsModel();
                        xlsModel.RowIndex = rowIndex;
                        xlsModel.Title = "";
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModels.Add(xlsModel);

                        xlsModel = new Model.XlsModel();
                        xlsModel.RowIndex = rowIndex;
                        xlsModel.Title = sum1.ToString();
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModels.Add(xlsModel);

                        xlsModel = new Model.XlsModel();
                        xlsModel.RowIndex = rowIndex;
                        xlsModel.Title = sum2.ToString();
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModels.Add(xlsModel);


                        xlsModel = new Model.XlsModel();
                        xlsModel.RowIndex = rowIndex;
                        xlsModel.Title = sum3.ToString();
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModels.Add(xlsModel);

                        xlsModel = new Model.XlsModel();
                        xlsModel.RowIndex = rowIndex;
                        xlsModel.Title = sum4.ToString();
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModels.Add(xlsModel);


                        xlsModel = new Model.XlsModel();
                        xlsModel.RowIndex = rowIndex;
                        xlsModel.Title = sum5.ToString();
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModels.Add(xlsModel);

                        xlsModel = new Model.XlsModel();
                        xlsModel.RowIndex = rowIndex;
                        xlsModel.Title = sum6.ToString();
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModels.Add(xlsModel);
                    }
                    
                }

            }

            return xlsModels;
        }

    }
}
